Private Sub mnuTableParameters_Click()
Dim result%, jobnum%, mainjob%, paramCount%, paramN%, textHandle&, TextLength%
Dim paramText$
Dim TableType As PETableType, LogOnInfo As PELogOnInfo
' Modifying table parameters is a feature available only to Crystal Pro
If Not CRYSTAL_PRO Then
MsgBox "Options are a Crystal Pro only feature.", vbOKOnly + vbCritical, "Crystal Pro Only"
Exit Sub
End If
result% = PEOpenEngine()
If result% = 0 Then
MsgBox "Could not start the report engine. Execution must halt.", vbOKOnly + vbCritical, "Serious Error"
End
End If
jobnum% = PEOpenPrintJob(lblReportName.Caption) ' Name from label on sample form
ErrorTrap "OpenPrintJob in TableParameters", jobnum%
' Subreport check - if a subreport is currently selected on the main form, jobnum% becomes the subreport
If lblSubreportName.Visible Then
mainjob% = jobnum%
jobnum% = PEOpenSubreport(mainjob%, lblSubreportName.Caption)
ErrorTrap "OpenSubReport in TableParameters", mainjob%
End If
' Parameters only exist when the table type is SQL - standard table types don't have the queries
' Note that access databases can be connected as a standard type or as an SQL type through ODBC
' This example is not designed to handle multiple data sources on a single report - it presumes
' that all tables are standard or SQL, and if SQL, from one database
' Define the size of the structures
TableType.StructSize = PE_SIZEOF_TABLE_TYPE
LogOnInfo.StructSize = PE_SIZEOF_LOGON_INFO
result% = PEGetNthTableType(jobnum%, 0, TableType)
ErrorTrap "GetNthTableType in TableParameters", jobnum%
If TableType.DBType = PE_DT_STANDARD Then
MsgBox "SQL Queries are only available for SQL databases. This report is attached to a standard database. Select a different report.", vbOKOnly + vbCritical, "SQL Databases Only!"
Else
' Now that it is confirmed that this is an SQL database, we will attempt to get logon information
' from the crPEGetNthTableLogOnInfo call - this data will be offered as default for the Logon Server
' parameters
result% = crPEGetNthTableLogOnInfo(jobnum%, 0, logOnInfo.ServerName, logOnInfo.DatabaseName, logOnInfo.UserID)
ErrorTrap "GetNthTableLogOnInfo in TableParameters", jobnum%
' Get the fields needed for the LogOn Server call from the user, defaulting with the data
' from the TableLogOnInfo call
LogOnInfo.ServerName = InputBox$("Enter the name of the Server:", "Server Name", LogOnInfo.ServerName) & Chr$(0)
LogOnInfo.DatabaseName = InputBox$("Enter the name of the Database:", "Database Name", LogOnInfo.DatabaseName) & Chr$(0)
LogOnInfo.UserID = InputBox("Enter the User ID:", "User ID", LogOnInfo.UserID) & Chr$(0)
LogOnInfo.Password = InputBox("Enter the Password:", "Password", LogOnInfo.Password) & Chr$(0)
' Attempt to log on server using parameters
result% = crPELogOnServer(tableType.DLLName, logOnInfo.ServerName, logOnInfo.DatabaseName, logOnInfo.UserID, logOnInfo.Password)
ErrorTrap "LogOnServer in TableParameters", jobnum%
' Get number of parameters
paramCount% = PEGetNParams(jobnum%)
ErrorTrap "GetNParams in TableParameters", jobnum%
If paramCount% < 1 Then
MsgBox "There are no parameters in this report. Execution must halt.", vbOKOnly + vbCritical, "No Parameters"
Else
' If there are parameters, load them into the sections form
Load Sections
CenterForm Sample, Sections
Sections.Tag = "Query Parameter List"
For paramN% = 0 To paramCount% - 1
' Get parameter
result% = PEGetNthParam(jobnum%, paramN%, textHandle&, TextLength%)
' Set chr$(0) into the fieldname string based on the length retrieved in the preceding call
' NOTE! Failure to do this step results in General Protection Faults
paramText$ = String$(TextLength%, 0)
' crvbHandleToBstr fills the fieldname string with SQL Query text
result% = crvbHandleToBstr(textHandle&, paramText$, TextLength%)
ErrorTrap "HandleToBstr for paramText in TableParameters", jobnum%
' Put parameter into sections form
Sections!lstSections.AddItem paramText$
Next paramN%
' Show form 1 ' Modally
Sections.Show 1 ' Modal
Select Case Sections.Tag
Case "Ok"
If MsgBox("Set changes to parameters into print job?", vbYesNo + vbQuestion, "Change Parameters?") = vbYes Then
' Set up loop to set parameter changes into print job
For paramN% = 0 To Sections!lstSections.ListCount - 1
' Get parameter from list box on sections form, add null to end for termination
paramText$ = Sections!lstSections.List(paramN%) & Chr$(0)
result% = PESetNthParam(jobnum%, paramN%, paramText$)
ErrorTrap "SetNthParam in TableParameters", jobnum%
Next paramN%
End If
Case "Edit"
Sections!lstSections.List(Sections!lstSections.ListIndex) = InputBox$("Enter parameter data for this parameter:", "Enter Parameter Data", Sections!lstSections.List(Sections!lstSections.ListIndex))
End Select
Unload Sections
End If
' Log off Server
result% = crPELogOffServer(tableType.DLLName, logOnInfo.ServerName, logOnInfo.DatabaseName, logOnInfo.UserID, logOnInfo.Password)
ErrorTrap "LogOffServer in TableParameters", jobnum%
End If
' Offer opportunity to see what you did to the report
If MsgBox("Do you want to preview the report?", vbYesNo + vbQuestion, "Preview Report?") = vbYes Then
' Simplified version of the custom-l ink preview routine (no custom buttons)
result% = PEOutputToWindow(jobnum%, "Parameters Demonstration Preview" & Chr$(0), CW_USEDEFAULT, CW_USEDEFAULT, CW_USEDEFAULT, CW_USEDEFAULT, 0, 0)
ErrorTrap "OutputtoWindow in TableParameters", jobnum%
result% = PEStartPrintJob(jobnum%, True)
ErrorTrap "StartPrintJob in TableParameters", jobnum%
result% = 1
Do While result% <> 0
DoEvents
DoEvents
result% = PEGetWindowHandle(jobnum%)
Loop
End If
' Close print job and engine
' Subreport check - if a subreport is currently open, call PreviewReport to offer a chance to
' preview the main report, then close the subreport and main report
If lblSubreportName.Visible Then
PreviewReport mainjob%
result% = PECloseSubreport(jobnum%)
ErrorTrap "CloseSubReport in TableParameters", mainjob%
PEClosePrintJob mainjob%
Else
PEClosePrintJob jobnum%
End If
PECloseEngine
MsgBox "Query Parameters Complete!", vbOKOnly, "Operation Succeeded"
End Sub
ActiveX
Private Sub mnuTableParameters_Click()
Dim ParamN As Integer, GetParamN As String
Dim hwndPreviewWindow As Long
' Modifying table parameters is a feature available only to Crystal Pro
If Not CRYSTAL_PRO Then
MsgBox "Options are a Crystal Pro only feature.", vbOKOnly + vbCritical, "Crystal Pro Only"
Exit Sub
End If
CrystalReport1.ReportFileName = lblReportName.Caption ' Name from label on sample form
' Set up endless loop (only ends with an Exit Do) for editing multiple parameters
Do While True
GetParamN = InputBox("Enter parameter number to edit. Press Cancel to end editing of parameters:", "Enter Parameter Number", "0")
' if a zero length string, then Cancel was pressed, exit the loop
If GetParamN = "" Then Exit Do
' Otherwise a parameter number was entered, display a input box for the parameter
ParamN = Val(GetParamN)
GetParamN = InputBox("Enter value for parameter number " & ParamN & ". Press Cancel to abort editing of parameter:", "Enter Parameter", CrystalReport1.StoredParamProc(ParamN))
If GetParamN = "" Then Exit Do
CrystalReport1.StoredProcParam(ParamN) = GetParamN
Loop
' Offer opportunity to see what you did to the report
If MsgBox("Do you want to preview the report?", vbYesNo + vbQuestion, "Preview Report?") = vbYes Then
CrystalReport1.Destination = 0 ' Window
CrystalReport1.Action = 1 ' Print
ErrorTrap "TableParameters"
hwndPreviewWindow = GetActiveWindow()
Do While IsWindow(hwndPreviewWindow)
DoEvents
Loop
End If
' Close the report
CrystalReport1.ReportFileName = ""
MsgBox "Query Parameters Complete!", vbOKOnly, "Operation Completed"
End Sub
Seagate Software IMG Holdings, Inc. http://www.seagatesoftware.com Support services: http://support.seagatesoftware.com |